package com.medata.utils;

import lombok.Data;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;

/**
 * @author ：zb
 * @date ：Created in 2019/8/12 0012 19:17
 * @description：
 */

@Data
public class ExcelUtils {
    public static void getValues(ResultSet rs, List<List<String>> values, List<String> keys, int haskey, int code) {
        try {
            while (rs.next()) {
                List<String> value = new ArrayList<>();
                String xml = rs.getString(3);
                xml = xml.replaceAll("&#", "");
                xml = xml.replaceAll("&", "");
                xml = xml.replaceAll("#", "");
                Document document = DocumentHelper.parseText(xml);
                Element root = document.getRootElement();
                List<Element> list = root.elements();
                for (Element e : list) {
                    String text = e.getText();
                    value.add(text);
                    if (haskey == 0) {
                        String name = e.attributeValue("FieldDesc");
                        keys.add(name);
                    }
                }
                haskey = 1;
//                value.add(rs.getString(6));
                value.add(rs.getString(7));
//                if (code == 0) {
//                    value.add("等待审核");
//                } else {
//                    value.add("通过审核");
//                }
                values.add(value);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        ExcelUtils.preExcels();
    }

    //多线程导出excel
    public static void preExcels() {
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
//            Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.1.72:1433/yhcd", "sa", "sa");
            Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://10.55.101.192:1433/yhcd", "maida", "qaz_123!@#");
            Statement statement = connection.createStatement();
            String[] dbas={"words","dyx"};
            for (String dba:dbas){
                File file=new File("c:/yun/excels/"+dba);
                if (!file.exists()){
                    file.mkdirs();
                }

                String countSql = "select count(*) from "+dba;
                ResultSet countRs = statement.executeQuery(countSql);
                System.out.println(LocalTime.now());
                while (countRs.next()) {
                    int counts = countRs.getInt(1);
                    List<excelRun> list = new ArrayList<>();
                    //3w数据一个线程
                    for (int i = 0; i < 10; i++) {
                        int start = 0 + i * 30000;
                        int end = start + 30000;
                        if (start<counts){
                            excelRun run = new excelRun(start, end,dba);
                            list.add(run);
                        }
                    }
                    for (excelRun run : list) {
                        new Thread(run).start();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Data
    //3w数据一个线程
    static class excelRun implements Runnable {
        private Integer startFlag;
        private Integer endFlag;
        private String dba;
        public excelRun(Integer startFlag, Integer endFlag,String dba) {
            this.startFlag = startFlag;
            this.endFlag = endFlag;
            this.dba=dba;
        }
        public excelRun() {
        }
        @Override
        public void run() {
            try {
                //连接服务器
                Class.forName("net.sourceforge.jtds.jdbc.Driver");
//                Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://192.168.1.72:1433/yhcd", "sa", "sa");
                Connection connection = DriverManager.getConnection("jdbc:jtds:sqlserver://10.55.101.192:1433/yhcd", "maida", "qaz_123!@#");
                Statement statement = connection.createStatement();
                while (startFlag < endFlag) {
                    String sf = String.valueOf(startFlag);
                    String fg = String.valueOf(startFlag + 5000);
                    System.out.println(sf);
                    String sql = "select * from "+dba+" where docid > " + startFlag + " and docid < =" + (startFlag + 5000);
                    ResultSet resultSet = statement.executeQuery(sql);
                    SXSSFWorkbook workbook = new SXSSFWorkbook();
                    SXSSFSheet sxssfSheet = workbook.createSheet();
                    workbook.setSheetName(0, dba+"_" + sf + "_" + fg);
                    List<List<String>> values = new ArrayList<>();
                    List<String> keys = new ArrayList<>();
                    int haskey = 0;
                    getValues(resultSet, values, keys, haskey, 0);
                    keys.add("更新时间");
                    if (values.size()>0){
                        SXSSFRow sxssfRow = sxssfSheet.createRow(0);
                        for (int i = 0; i < keys.size(); i++) {
                            SXSSFCell sxssfCell = sxssfRow.createCell(i);
                            sxssfCell.setCellValue(keys.get(i));
                        }
                        int rownum = 1;
                        for (List<String> va : values) {
                            SXSSFRow sxssfRow1 = sxssfSheet.createRow(rownum);
                            for (int i = 0; i < va.size(); i++) {
                                SXSSFCell cell = sxssfRow1.createCell(i);
                                cell.setCellValue(va.get(i));
                            }
                            rownum++;
                        }
                        File excelFile = new File("c:/yun/excels/"+dba+"/" + sf + "_" + fg + ".xls");
                        FileOutputStream fos = new FileOutputStream(excelFile);
                        workbook.write(fos);
                        workbook.close();
                    }
                    startFlag += 5000;
                }
                System.out.println(LocalTime.now());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}
